Hive Full Join多个表与Union All多个表

2023-08-17 10:07| 来源: 网络整理| 查看: 265

一.引言

Table A：用户在超市近一个月购买果蔬的记录

Table B：用户在超市近一个月购买饮品的记录

Table C：用户在超市近一个月购买零食的记录

先建一个表供我们插入数据

hive -e " create table if not exists user_act_of_buying ( user string, fruits string, drinks string, snacks string ) PARTITIONED BY(dt string) row format delimited fields terminated by '\t' "

二.Full Join（适合两个表）

遇到上述场景的第一反应就是使用FullJoin，以用户名为聚合字段进行联合，这里采用套娃的方式，将Table A 和 Table B结合再将结合的表与Table C结合得到最终结果，因为Hive没办法使用连等号： a.user = b.user = c.user，所以只能通过嵌套的方式。

hive -e " select tmp.*,c.* from (select user,fruit,drink from (select user,fruits from tableA where dt=$dt) a FULL OUTER JOIN (select user,drinks from tableB where dt=$dt) b on a.user = b.user ) tmp FULL OUTER JOIN (select user,snacks from tableC where dt=$dt) c on tmp.user = c.user; "

三.Union All（推荐👍 适合3个及以上表）

嵌套的方式可以解决问题，但是也有新的问题就是当需要Full Join的表太多时，需要写多层嵌套，不美观且容易出错。可以改变思路，先将全部的user Union在一起当做主表，然后依次LEFT OUTER JOIN Table A，Table B 和 Table C即可，即使还要结合用户购买的生活用品，电器等等，也不用担心嵌套太多不好写的问题，只需要将对应表的 user 添加到主表中，并把新增的表加入LEFT OUTER JOIN的队伍里即可。

hive -e " insert overwrite table user_act_of_buying partition(dt=$dt) select all.user,fruit.fruits,drinl.drinks,snack.snacks from ( select user from ( select user from (select user from tableA where dt=$dt) a UNION ALL select user from (select user from tableB where dt=$dt) b UNINO ALL select user from (select user from tableC where dt=$dt) c ) tmp group by user ) all LEFT OUTER JOIN (select user,fruit from tableA where dt=$dt) fruit on all.user = fruit.user LEFT OUTER JOIN (select user,drink from tableB where dt=$dr) drink on all.user = drink.user LEFT OUTER JOIN (select user,snacks from tableC where dt=$dt) snack on all.user = all.snack; "

这里先通过tmp拿到所有表的user字段，然后再通过group by去重保证一人一条记录，最后LEFT JOIN即可。这里有几个坑要注意：

1.Sql支持Union 和 UnionAll，其中Union可以达到去重的效果，但Hive只支持Union ALL，所以这里有去重需求需要使用Group By。

2.Union All的时候每个语句返回的列的数量和名字必须一样，这也是为什么union all时在select语句外又重复了一条select user语句。

3.Union All的时候每个表使用别名，否则容易报 missing EOF ，找了一下午空格，很坑。。

4.Union all的每张表不支持limit、order by等函数，但是可以添加很多where过滤条件，这里只是限制了dt 。

5.这里第一种方案只是demo，拿到的是所有的数据，所以没有执行insert操作，但是Union方法可以确定拿到的每个字段，所以支持插入到对应表。

6.LEFT OUTER JOIN 和 LEFT JOIN ，FULL JOIN 和 FULL OUTER JOIN效果都相同。

7.FULL OUTER JOIN 其实就是把 LEFT OUTER JOIN 和 RIGHT OUTER JOIN的结果结合在一起。

4.总结

别用 Full Join

【本文地址】

公司简介

联系我们